package statement

import (
	"errors"
	"fmt"
	"gitee.com/KotlinToGo/xorm/builder"
	"gitee.com/KotlinToGo/xorm/core"
	"gitee.com/KotlinToGo/xorm/dialect"
	"gitee.com/KotlinToGo/xorm/internal/utils"
	"gitee.com/KotlinToGo/xorm/schema"
	"reflect"
	"regexp"
	"strings"
)

func (s *Statement) genSelectSql(dialect dialect.IDialect, rownumber string) string {

	var sql = s.RawSQL
	var orderBys = s.OrderStr
	pLimitN := s.LimitN

	if dialect.URI().DBType != schema.Mssql && dialect.URI().DBType != schema.Oracle {
		if s.Start > 0 {
			sql = fmt.Sprintf("%v LIMIT %v OFFSET %v", sql, s.LimitN, s.Start)
			if pLimitN != nil {
				sql = fmt.Sprintf("%v LIMIT %v OFFSET %v", sql, *pLimitN, s.Start)
			} else {
				sql = fmt.Sprintf("%v LIMIT 0 OFFSET %v", sql, *pLimitN)
			}
		} else if pLimitN != nil {
			sql = fmt.Sprintf("%v LIMIT %v", sql, s.LimitN)
		}
	} else if dialect.URI().DBType == schema.Oracle {
		if s.Start != 0 || pLimitN != nil {
			sql = fmt.Sprintf("SELECT aat.* FROM (SELECT at.*,ROWNUM %v FROM (%v) at WHERE ROWNUM <= %d) aat WHERE %v > %d",
				rownumber, sql, s.Start+*pLimitN, rownumber, s.Start)
		}
	} else {
		keepSelect := false
		var fullQuery string
		if s.Start > 0 {
			fullQuery = fmt.Sprintf("SELECT sq.* FROM (SELECT ROW_NUMBER() OVER (ORDER BY %v) AS %v,", orderBys, rownumber)
		} else if pLimitN != nil {
			fullQuery = fmt.Sprintf("SELECT TOP %d", *pLimitN)
		} else {
			keepSelect = true
		}

		if !keepSelect {
			expr := `^\s*SELECT\s*`
			reg, err := regexp.Compile(expr)
			if err != nil {
				fmt.Println(err)
			}
			sql = strings.ToUpper(sql)
			if reg.MatchString(sql) {
				str := reg.FindAllString(sql, -1)
				fullQuery = fmt.Sprintf("%v %v", fullQuery, sql[len(str[0]):])
			}
		}

		if s.Start > 0 {
			// T-SQL offset starts with 1, not like MySQL with 0;
			if pLimitN != nil {
				fullQuery = fmt.Sprintf("%v) AS sq WHERE %v BETWEEN %d AND %d", fullQuery, rownumber, s.Start+1, s.Start+*pLimitN)
			} else {
				fullQuery = fmt.Sprintf("%v) AS sq WHERE %v >= %d", fullQuery, rownumber, s.Start+1)
			}
		} else {
			fullQuery = fmt.Sprintf("%v ORDER BY %v", fullQuery, orderBys)
		}

		if keepSelect {
			if len(orderBys) > 0 {
				sql = fmt.Sprintf("%v ORDER BY %v", sql, orderBys)
			}
		} else {
			sql = fullQuery
		}
	}

	return sql
}

func (s *Statement) GenQuerySQL(sqlOrArgs ...interface{}) (string, []interface{}, error) {
	if len(sqlOrArgs) > 0 {
		return s.ConvertSQLOrArgs(sqlOrArgs...)
	}

	if s.RawSQL != "" {
		var dial = s.dialect
		rownumber := "xorm" + utils.NewShortUUID().String()
		sql := s.genSelectSql(dial, rownumber)

		params := s.RawParams
		i := len(params)

		//		var result []map[string]interface{}
		//		var err error
		if i == 1 {
			vv := reflect.ValueOf(params[0])
			if vv.Kind() != reflect.Ptr || vv.Elem().Kind() != reflect.Map {
				return sql, params, nil
			} else {
				sqlStr1, param, _ := core.MapToSlice(sql, params[0])
				return sqlStr1, param, nil
			}
		} else {
			return sql, params, nil
		}
		//		return session.statement.RawSQL, session.statement.RawParams, nil
	}

	if len(s.TableName()) <= 0 {
		return "", nil, ErrTableNotFound
	}

	var columnStr = s.ColumnStr()
	if len(s.SelectStr) > 0 {
		columnStr = s.SelectStr
	} else {
		if s.JoinStr == "" {
			if columnStr == "" {
				if s.GroupByStr != "" {
					columnStr = s.quoteColumnStr(s.GroupByStr)
				} else {
					columnStr = s.genColumnStr()
				}
			}
		} else {
			if columnStr == "" {
				if s.GroupByStr != "" {
					columnStr = s.quoteColumnStr(s.GroupByStr)
				} else {
					columnStr = "*"
				}
			}
		}
		if columnStr == "" {
			columnStr = "*"
		}
	}

	if err := s.ProcessIDParam(); err != nil {
		return "", nil, err
	}

	sqlStr, condArgs, err := s.genSelectSQL(columnStr, true, true)
	if err != nil {
		return "", nil, err
	}
	args := append(s.joinArgs, condArgs...)

	// for mssql and use limit
	qs := strings.Count(sqlStr, "?")
	if len(args)*2 == qs {
		args = append(args, args...)
	}

	return sqlStr, args, nil
}

func (s *Statement) GenSumSQL(bean interface{}, columns ...string) (string, []interface{}, error) {
	if s.RawSQL != "" {
		return s.GenRawSQL(), s.RawParams, nil
	}

	s.SetRefBean(bean)

	var sumStrs = make([]string, 0, len(columns))
	for _, colName := range columns {
		if !strings.Contains(colName, " ") && !strings.Contains(colName, "(") {
			colName = s.quote(colName)
		} else {
			colName = s.ReplaceQuote(colName)
		}
		sumStrs = append(sumStrs, fmt.Sprintf("COALESCE(sum(%s),0)", colName))
	}
	sumSelect := strings.Join(sumStrs, ", ")

	if err := s.mergeCond(bean); err != nil {
		return "", nil, err
	}

	sqlStr, condArgs, err := s.genSelectSQL(sumSelect, true, true)
	if err != nil {
		return "", nil, err
	}

	return sqlStr, append(s.joinArgs, condArgs...), nil
}

func (s *Statement) GenGetSQL(bean interface{}) (string, []interface{}, error) {
	v := rValue(bean)
	isStruct := v.Kind() == reflect.Struct
	if isStruct {
		s.SetRefBean(bean)
	}

	var columnStr = s.ColumnStr()
	if len(s.SelectStr) > 0 {
		columnStr = s.SelectStr
	} else {
		// TODO: always generate column names, not use * even if join
		if len(s.JoinStr) == 0 {
			if len(columnStr) == 0 {
				if len(s.GroupByStr) > 0 {
					columnStr = s.quoteColumnStr(s.GroupByStr)
				} else {
					columnStr = s.genColumnStr()
				}
			}
		} else {
			if len(columnStr) == 0 {
				if len(s.GroupByStr) > 0 {
					columnStr = s.quoteColumnStr(s.GroupByStr)
				}
			}
		}
	}

	if len(columnStr) == 0 {
		columnStr = "*"
	}

	if isStruct {
		if err := s.mergeCond(bean); err != nil {
			return "", nil, err
		}
	} else {
		if err := s.ProcessIDParam(); err != nil {
			return "", nil, err
		}
	}

	sqlStr, condArgs, err := s.genSelectSQL(columnStr, true, true)
	if err != nil {
		return "", nil, err
	}

	return sqlStr, append(s.joinArgs, condArgs...), nil
}

// GenCountSQL generates the SQL for counting
func (s *Statement) GenCountSQL(beans ...interface{}) (string, []interface{}, error) {
	if s.RawSQL != "" {
		return s.GenRawSQL(), s.RawParams, nil
	}

	var condArgs []interface{}
	var err error
	if len(beans) > 0 {
		s.SetRefBean(beans[0])
		if err := s.mergeCond(beans[0]); err != nil {
			return "", nil, err
		}
	}

	var selectSQL = s.SelectStr
	if len(selectSQL) <= 0 {
		if s.IsDistinct {
			selectSQL = fmt.Sprintf("count(DISTINCT %s)", s.ColumnStr())
		} else if s.ColumnStr() != "" {
			selectSQL = fmt.Sprintf("count(%s)", s.ColumnStr())
		} else {
			selectSQL = "count(*)"
		}
	}
	sqlStr, condArgs, err := s.genSelectSQL(selectSQL, false, false)
	if err != nil {
		return "", nil, err
	}

	return sqlStr, append(s.joinArgs, condArgs...), nil
}

func (s *Statement) genSelectSQL(columnStr string, needLimit, needOrderBy bool) (string, []interface{}, error) {
	var (
		distinct                  string
		dial                      = s.dialect
		quote                     = s.quote
		fromStr                   = " FROM "
		top, mssqlCondi, whereStr string
	)
	if s.IsDistinct && !strings.HasPrefix(columnStr, "count") {
		distinct = "DISTINCT "
	}

	condSQL, condArgs, err := s.GenCondSQL(s.cond)
	if err != nil {
		return "", nil, err
	}
	if len(condSQL) > 0 {
		whereStr = " WHERE " + condSQL
	}

	if dial.URI().DBType == schema.Mssql && strings.Contains(s.TableName(), "..") {
		fromStr += s.TableName()
	} else {
		fromStr += quote(s.TableName())
	}

	if s.TableAlias != "" {
		if dial.URI().DBType == schema.Oracle {
			fromStr += " " + quote(s.TableAlias)
		} else {
			fromStr += " AS " + quote(s.TableAlias)
		}
	}
	if s.JoinStr != "" {
		fromStr = fmt.Sprintf("%v %v", fromStr, s.JoinStr)
	}

	pLimitN := s.LimitN
	if dial.URI().DBType == schema.Mssql {
		if pLimitN != nil {
			LimitNValue := *pLimitN
			top = fmt.Sprintf("TOP %d ", LimitNValue)
		}
		if s.Start > 0 {
			var column string
			if len(s.RefTable.PKColumns()) == 0 {
				for _, index := range s.RefTable.Indexes {
					if len(index.Cols) == 1 {
						column = index.Cols[0]
						break
					}
				}
				if len(column) == 0 {
					column = s.RefTable.ColumnsSeq()[0]
				}
			} else {
				column = s.RefTable.PKColumns()[0].Name
			}
			if s.needTableName() {
				if len(s.TableAlias) > 0 {
					column = s.TableAlias + "." + column
				} else {
					column = s.TableName() + "." + column
				}
			}

			var orderStr string
			if needOrderBy && len(s.OrderStr) > 0 {
				orderStr = " ORDER BY " + s.OrderStr
			}

			var groupStr string
			if len(s.GroupByStr) > 0 {
				groupStr = " GROUP BY " + s.GroupByStr
			}
			mssqlCondi = fmt.Sprintf("(%s NOT IN (SELECT TOP %d %s%s%s%s%s))",
				column, s.Start, column, fromStr, whereStr, orderStr, groupStr)
		}
	}

	var buf strings.Builder
	fmt.Fprintf(&buf, "SELECT %v%v%v%v%v", distinct, top, columnStr, fromStr, whereStr)
	if len(mssqlCondi) > 0 {
		if len(whereStr) > 0 {
			fmt.Fprint(&buf, " AND ", mssqlCondi)
		} else {
			fmt.Fprint(&buf, " WHERE ", mssqlCondi)
		}
	}

	if s.GroupByStr != "" {
		fmt.Fprint(&buf, " GROUP BY ", s.GroupByStr)
	}
	if s.HavingStr != "" {
		fmt.Fprint(&buf, " ", s.HavingStr)
	}
	if needOrderBy && s.OrderStr != "" {
		fmt.Fprint(&buf, " ORDER BY ", s.OrderStr)
	}
	if needLimit {
		if dial.URI().DBType != schema.Mssql && dial.URI().DBType != schema.Oracle {
			if s.Start > 0 {
				if pLimitN != nil {
					fmt.Fprintf(&buf, " LIMIT %v OFFSET %v", *pLimitN, s.Start)
				} else {
					fmt.Fprintf(&buf, "LIMIT 0 OFFSET %v", s.Start)
				}
			} else if pLimitN != nil {
				fmt.Fprint(&buf, " LIMIT ", *pLimitN)
			}
		} else if dial.URI().DBType == schema.Oracle {
			if s.Start != 0 || pLimitN != nil {
				oldString := buf.String()
				buf.Reset()
				rawColStr := columnStr
				if rawColStr == "*" {
					rawColStr = "at.*"
				}
				fmt.Fprintf(&buf, "SELECT %v FROM (SELECT %v,ROWNUM RN FROM (%v) at WHERE ROWNUM <= %d) aat WHERE RN > %d",
					columnStr, rawColStr, oldString, s.Start+*pLimitN, s.Start)
			}
		}
	}
	if s.IsForUpdate {
		return dial.ForUpdateSQL(buf.String()), condArgs, nil
	}

	return buf.String(), condArgs, nil
}

func (s *Statement) GenExistSQL(bean ...interface{}) (string, []interface{}, error) {
	if s.RawSQL != "" {
		return s.GenRawSQL(), s.RawParams, nil
	}

	var sqlStr string
	var args []interface{}
	var joinStr string
	var err error
	if len(bean) == 0 {
		tableName := s.TableName()
		if len(tableName) <= 0 {
			return "", nil, ErrTableNotFound
		}

		tableName = s.quote(tableName)
		if len(s.JoinStr) > 0 {
			joinStr = s.JoinStr
		}

		if s.Cond().IsValid() {
			condSQL, condArgs, err := s.GenCondSQL(s.Cond())
			if err != nil {
				return "", nil, err
			}

			if s.dialect.URI().DBType == schema.Mssql {
				sqlStr = fmt.Sprintf("SELECT TOP 1 * FROM %s %s WHERE %s", tableName, joinStr, condSQL)
			} else if s.dialect.URI().DBType == schema.Oracle {
				sqlStr = fmt.Sprintf("SELECT * FROM %s WHERE (%s) %s AND ROWNUM=1", tableName, joinStr, condSQL)
			} else {
				sqlStr = fmt.Sprintf("SELECT * FROM %s %s WHERE %s LIMIT 1", tableName, joinStr, condSQL)
			}
			args = condArgs
		} else {
			if s.dialect.URI().DBType == schema.Mssql {
				sqlStr = fmt.Sprintf("SELECT TOP 1 * FROM %s %s", tableName, joinStr)
			} else if s.dialect.URI().DBType == schema.Oracle {
				sqlStr = fmt.Sprintf("SELECT * FROM  %s %s WHERE ROWNUM=1", tableName, joinStr)
			} else {
				sqlStr = fmt.Sprintf("SELECT * FROM %s %s LIMIT 1", tableName, joinStr)
			}
			args = []interface{}{}
		}
	} else {
		beanValue := reflect.ValueOf(bean[0])
		if beanValue.Kind() != reflect.Ptr {
			return "", nil, errors.New("needs a pointer")
		}

		if beanValue.Elem().Kind() == reflect.Struct {
			if err := s.SetRefBean(bean[0]); err != nil {
				return "", nil, err
			}
		}

		if len(s.TableName()) <= 0 {
			return "", nil, ErrTableNotFound
		}
		s.Limit(1)
		sqlStr, args, err = s.GenGetSQL(bean[0])
		if err != nil {
			return "", nil, err
		}
	}

	return sqlStr, args, nil
}

func (s *Statement) GenFindSQL(autoCond builder.Cond) (string, []interface{}, error) {
	if s.RawSQL != "" {
		return s.GenRawSQL(), s.RawParams, nil
	}

	var sqlStr string
	var args []interface{}
	var err error

	if len(s.TableName()) <= 0 {
		return "", nil, ErrTableNotFound
	}

	var columnStr = s.ColumnStr()
	if len(s.SelectStr) > 0 {
		columnStr = s.SelectStr
	} else {
		if s.JoinStr == "" {
			if columnStr == "" {
				if s.GroupByStr != "" {
					columnStr = s.quoteColumnStr(s.GroupByStr)
				} else {
					columnStr = s.genColumnStr()
				}
			}
		} else {
			if columnStr == "" {
				if s.GroupByStr != "" {
					columnStr = s.quoteColumnStr(s.GroupByStr)
				} else {
					columnStr = "*"
				}
			}
		}
		if columnStr == "" {
			columnStr = "*"
		}
	}

	s.cond = s.cond.And(autoCond)

	sqlStr, condArgs, err := s.genSelectSQL(columnStr, true, true)
	if err != nil {
		return "", nil, err
	}
	args = append(s.joinArgs, condArgs...)
	// for mssql and use limit
	qs := strings.Count(sqlStr, "?")
	if len(args)*2 == qs {
		args = append(args, args...)
	}

	return sqlStr, args, nil
}
